﻿CREATE PROCEDURE [usix].[s_Comments_Export]
--	@DaysBack int = 3
@LoanID int = NULL
AS

print DATEPART(hh,GETDATE())
IF 1=0 AND NOT (DATEPART(hh,GETDATE()) BETWEEN 22 AND 23 OR DATEPART(hh,GETDATE()) BETWEEN 0 AND 6)BEGIN
	RAISERROR('[usix].[s_Comments_Export] must be run between 23:00 and 7:00.',16,1) RETURN
END
EXEC sh_Debug_Print 'Do usix.t_Comment_Export'
BEGIN TRAN
DECLARE @MaxDate datetime,@UsixID varchar(8),@CID int
SELECT @MaxDate = MAX(NoteDate) FROM dbo.t_Note WHERE StatusID = dbo.fs_StateID__Notes__ExportedFromUSIX() AND ISNULL(@LoanID,0) IN (0,LoanID)
IF @MaxDate IS NULL SET @MaxDate = CASE WHEN @LoanID IS NULL THEN '7/17/08' ELSE '1/1/1999' END
SET @MaxDate = '2008-09-23'
PRINT @MaxDate

TRUNCATE TABLE usix.t_Comment_Export
IF NOT @LoanID IS NULL
	SELECT @UsixID = UsixID,@CID = CompanyID_Raw FROM usix.t_CustomerFile_Export WHERE ISNULL(@LoanID,0) IN (0,LoanID)

INSERT INTO usix.t_Comment_Export([Id #],Date,Remark,[Due Date],CompanyID,ID,Comment,Agent,DateFull)
SELECT [Id #],Date,Remark,[Due Date],CompanyID,ID,
	ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'(.+)[[(][A-Z]\w{2,3}\s*-\s*\d\d:\d\d[:\d]*[])]$',1),''),Remark)Comment,
	ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'[[(]([A-Z]\w{2,3})\s*-\s*\d\d:\d\d[])]$',1),''),'USIX')Agent,
	Date+CONVERT(datetime,ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'[[(][A-Z]\w{2,3}\s*-\s*(\d\d:\d\d[:\d]*)[])]$',1),''),'00:00:00'))DateFull
FROM DataMining.dbo.ALL_Comments WITH(nolock)
WHERE Date BETWEEN dbo.Today(@MaxDate)+1 AND dbo.clr_Date(GETDATE()) AND ISNULL(@UsixID,'') IN ('',[Id #]) AND ISNULL(@CID,0) IN (0,CompanyID)
ORDER BY Date+CONVERT(datetime,ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'[[(][A-Z]\w{2,3}\s*-\s*(\d\d:\d\d[:\d]*)[])]$',1),''),'00:00:00'))

IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END

EXEC sh_Debug_Print 'Do usix.t_Note'
SELECT IDENTITY(int)ID,[Id #],CompanyID,usix.Comment_Export([Id #],CompanyID,Agent,DateFull)Comment,LEFT(MAX(Agent),4)Agent,
DATEADD(ms,RAND(MAX(ID)*10)*100000,DateFull)DateFull, MAX([Due Date]) DueDate,RAND(MAX(ID)*10) R
INTO #Note
FROM usix.t_Comment_Export C_E --WHERE Agent <> 'USIX' --AND dbo.Today(DateFull) > GETDATE() - @DaysBack
INNER JOIN (SELECT DISTINCT LoanID,UsixID,CompanyID_Raw FROM usix.t_CustomerFile_Export WHERE LoanID > 0) AS L_U
	ON C_E.[Id #] = L_U.UsixID AND C_E.CompanyID = L_U.CompanyID_Raw
GROUP BY C_E.[Id #],C_E.CompanyID,C_E.Agent,C_E.DateFull

--SELECT * INTO temp.t_Note FROM #Note COMMIT RETURN
--SELECT * FROM usix.t_Comment_Export
IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END

--UPDATE #Note SET Agent = usix.t_CollectorID.UserName FROM #Note INNER JOIN usix.t_CollectorID ON #Note.Agent = usix.t_CollectorID.UsixCID

--SELECT 
--L_U.LoanID,N.DateFull,COUNT(*)Count
--FROM #Note AS N
--INNER JOIN (SELECT DISTINCT LoanID,UsixID,CompanyID_Raw FROM usix.t_CustomerFile_Export WHERE LoanID > 0) AS L_U
--	ON N.[Id #] = L_U.UsixID AND N.CompanyID = L_U.CompanyID_Raw
--LEFT OUTER JOIN usix.t_CollectorID CID ON N.Agent = CID.UsixCID
--GROUP BY L_U.LoanID,N.DateFull
--HAVING COUNT(*) > 1

/* This is the code*/
EXECUTE AS Login = 'AFC-HOME\SQLServer'
INSERT INTO dbo.t_Note(
LoanID,Note,StatusID,
StatusDate,StatusUser,DateDue,DateDueBy,
NoteTypeID,NoteActionCodeID,Phone,PhoneCategoryID,NoteDate,NoteUser,IsResolved)
SELECT DISTINCT
L_U.LoanID,N.Comment, dbo.fs_StateID__Notes__ExportedFromUSIX(),
N.DateFull,ISNULL(CID.UserName,N.Agent), N.DueDate,ISNULL(CID.UserName,N.Agent),
10 AS NoteTypeID, 5 AS NoteActionCodeID,NULL,0,
--DATEADD(ms,RAND((ROW_NUMBER()OVER(ORDER BY L_U.LoanID))*1000),N.DateFull),
DATEADD(ms,L_U.LoanID,N.DateFull),
ISNULL(CID.UserName,N.Agent),1 IsRelolved
FROM #Note AS N
INNER JOIN (SELECT DISTINCT LoanID,UsixID,CompanyID_Raw FROM usix.t_CustomerFile_Export WHERE LoanID > 0) AS L_U
	ON N.[Id #] = L_U.UsixID AND N.CompanyID = L_U.CompanyID_Raw
INNER JOIN t_Loan L ON L.ID = L_U.LoanID
LEFT OUTER JOIN usix.t_CollectorID CID ON N.Agent = CID.UsixCID WHERE NOT L_U.LoanID IN (1013805,1014103)
REVERT

DROP TABLE #Note
COMMIT

RETURN
----------------------------------------------
--SELECT * FROM t_Note WHERE StatusID = dbo.fs_StateID__Notes__ExportedFromUSIX() AND NoteDate> '7/15/08'
--SELECT COUNT(*) FROM usix.t_Comment_Export WHERE Date = '7/15/08'
----------------------------------------------
DELETE usix.t_Note_Last_Full

INSERT INTO usix.t_Note_Last_Full
SELECT     N.ID, N.LoanID, N.Note, N.StatusID, N.StatusDate, N.StatusUser, N.DateDue, N.DateDueBy, N.NoteTypeID, N.NoteActionCodeID
FROM         usix.v_Note AS N INNER JOIN
(
SELECT  N.[Id #],N.CompanyID, MAX(N.ID) AS NoteID
FROM         (SELECT [Id #],CompanyID,    MAX(DateFull) AS StatusDate_Max
                       FROM          usix.t_Note
                       GROUP BY [Id #],CompanyID) AS NL INNER JOIN
                      usix.t_Note AS N ON NL.[Id #] = N.[Id #] AND NL.CompanyID = N.CompanyID AND 
                      NL.StatusDate_Max = N.DateFull
GROUP BY N.[Id #],N.CompanyID
)NL ON N.ID = NL.NoteID

--UPDATE    N_L_F
--SET              StatusUser = C.UsixCID, DateDueBy = C.UsixCID
--FROM         usix.t_Note_Last_Full AS N_L_F INNER JOIN
--                      usix.t_CollectorID AS C ON N_L_F.StatusUser = C.UsixCID


IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END

ROLLBACK

EXEC sh_Debug_Print 'Done'
